-- @owner: ningyali
-- @date: 2024-08-12
-- @testpoint: 表约束-主键

--step1:创建测试表; expect:执行成功
drop table if exists t_ustore_count_optim_0016 cascade;
create table t_ustore_count_optim_0016(
	c_id int, c_int  integer , c_bool bool,
	c_real real ,
	c_char char(300),c_varchar2 varchar2(4000) default lpad('ee2t2xnbzzkih',80,'etskbfw@p%my8tesjbemus6gnv3'),
	c_timestamp timestamp not null,
	constraint pk_count_optim_0051 primary key(c_id)
	) with (storage_type=ustore);

--step2:创建索引和序列; expect:执行成功
drop index if exists idx_ustore_count_optim_0016_01;
drop index if exists idx_ustore_count_optim_0016_02;
create index idx_ustore_count_optim_0016_01 on t_ustore_count_optim_0016(c_int ,c_varchar2);
create index idx_ustore_count_optim_0016_02 on t_ustore_count_optim_0016(c_timestamp);
drop sequence if exists seq_ustore_count_optim_0016;
create sequence seq_ustore_count_optim_0016 increment by 1 start with 100000;

--step3:插入表数据; expect:执行成功
begin
	for i in 1..1000 loop
	insert into t_ustore_count_optim_0016(c_id,c_int,c_bool,c_real,c_char,c_varchar2,c_timestamp)  values(seq_ustore_count_optim_0016.nextval,(i+3)%59,'true',i%17+i/6-4.9690546872 ,lpad('ee2t2xnbzzkih',50,'et'), 'txrl(on!kccx~op(y4um8n*t2)@'||i||'h@atfy7skq%tw0ud+tb*crm=x' ,to_timestamp('19-aug-2004 21:38:09.123000','dd-mon-yyyy hh24:mi:ss.ff'));
    end loop;
end;
/

--step4:表约束-主键; expect:执行成功
with tmp as (select  c_timestamp,c_varchar2, count(c_id) over(partition by c_timestamp order by c_varchar2) f1 from t_ustore_count_optim_0016)
select extract (month from c_timestamp) a1,upper(concat(c_varchar2,'sdjhvcg')) a2, count(f1) over(order by f1)  from tmp where c_timestamp between '1924-12-03'  and '1925-11-03' order by a1,a2 ;

select c_int, count(c_id) from t_ustore_count_optim_0016 group by  c_int having  2 in (select c_id from t_ustore_count_optim_0016) ;
explain analyze select c_int, count(c_id) from t_ustore_count_optim_0016 group by  c_int having  2 in (select c_id from t_ustore_count_optim_0016) ;

--step5:清理环境; expect:执行成功
drop index if exists idx_ustore_count_optim_0016_01;
drop index if exists idx_ustore_count_optim_0016_02;
drop table if exists t_ustore_count_optim_0016 cascade;
drop sequence if exists seq_ustore_count_optim_0016;